﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using SyncWaterForPeople.Model;
using System.Data.SqlClient;
using System.ComponentModel.DataAnnotations;
using System.Text;


namespace SyncWaterForPeople.DataAccess
{
    public class SyncStorage
    {
        private static string connectionString = @"Data Source=.\sqlexpress;Initial Catalog=WFPDB;Integrated Security=True;";

        public SyncStorage()
        { }

        public List<UserWS> GetUserList()
        {
            List<UserWS> userList = new List<UserWS>();

            SqlConnection connection = new SqlConnection(connectionString);

            using (var cmd = connection.CreateCommand())
            {
                try
                {
                    connection.Open();
                    cmd.CommandText = string.Format("SELECT U.UserID AS ID, U.Login AS Lgn, U.Password AS Pswrd, P.Names AS Name, P.LastNames AS LastName FROM [User] U, Person P WHERE U.UserId = P.PersonId");
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            UserWS us = new UserWS();
                            us.UserID = Convert.ToString(reader["ID"]);
                            us.Login = Convert.ToString(reader["Lgn"]);
                            us.Password = Convert.ToString(reader["Pswrd"]);

                            var n = Convert.ToString(reader["Name"]);
                            var ln = Convert.ToString(reader["LastName"]);

                            us.Name = string.Format("{0} {1}", n, ln);

                            userList.Add(us);
                        }
                        reader.Close();
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            return userList;
        }

        public List<KardexWS> GetKardexList()
        {
            List<KardexWS> kardexList = new List<KardexWS>();

            SqlConnection connection = new SqlConnection(connectionString);

            using (var cmd = connection.CreateCommand())
            {
                try
                {
                    connection.Open();
                    cmd.CommandText = string.Format(@"SELECT K.KardexId AS ID, K.HasAWaterMeasurer AS HasAWaterMeasurer, P.Names AS Name, P.LastNames AS LastName, A.AllotmentName AS Allotment, A.SquareId AS Square
													  FROM Kardex K, Person P, Allotments A 
													  WHERE K.ClientId = P.PersonId AND K.KardexId = A.KardexId AND K.Enable = 'true'");
                    
					using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            KardexWS us = new KardexWS();

                            us.KardexID = Convert.ToInt32(reader["ID"]);
                            us.FirstName = Convert.ToString(reader["Name"]);
                            us.LastName = Convert.ToString(reader["LastName"]);
							us.Allotment = Convert.ToString(reader["Allotment"]);
							us.Square = Convert.ToString(reader["Square"]);
							us.HasAWaterMeasurer = Convert.ToBoolean(reader["HasAWaterMeasurer"]);

                            kardexList.Add(us);
                        }
                        reader.Close();
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            return kardexList;
        }

        public List<CutSupplyWS> GetCutSupplies()
        {
            List<CutSupplyWS> cutSupplyList = new List<CutSupplyWS>();

            SqlConnection connection = new SqlConnection(connectionString);

            using (var cmd = connection.CreateCommand())
            {
                try
                {
                    connection.Open();
                    cmd.CommandText = "SELECT * FROM CutSupply";
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            CutSupplyWS cp = new CutSupplyWS();
                            cp.CutSupplyID = Convert.ToInt32 (reader ["CutSupplyID"]);
							cp.CurrentReading = Convert.ToInt32 (reader ["CurrentReading"]);
							cp.KardexId = Convert.ToInt32 (reader ["KardexId"]);
                            cp.SystemDate = Convert.ToDateTime(reader ["SystemDate"]);

                            cutSupplyList.Add(cp);
                        }
                        reader.Close();
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }

            return cutSupplyList;
        }

        public List<ReconnectionWS> GetReconnections()
        {
            List<ReconnectionWS> reconnectionsList = new List<ReconnectionWS>();

            SqlConnection connection = new SqlConnection(connectionString);

            using (var cmd = connection.CreateCommand())
            {
                try
                {
                    connection.Open();
					cmd.CommandText = "SELECT * FROM Reconnection WHERE Eneabled = 'false'";
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ReconnectionWS rc = new ReconnectionWS();

							rc.ReconnectionID = Convert.ToInt32 (reader["ReconnectionID"]);
							rc.CurrentReading = Convert.ToInt32 (reader["CurrentReading"]);
							rc.KardexId = Convert.ToInt32 (reader["KardexId"]);
                            rc.SystemDate = Convert.ToDateTime(reader["SystemDate"]);
							rc.Enabled = Convert.ToBoolean (reader ["Eneabled"]);

                            reconnectionsList.Add(rc);
                        }
                        reader.Close();
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }

            return reconnectionsList;
        }

		public decimal GetBaseWaterConsume()
		{
			decimal baseConsume = 0;
			
			SqlConnection connection = new SqlConnection(connectionString);
			
			using (var cmd = connection.CreateCommand())
			{
				try
				{
					connection.Open();
					cmd.CommandText = string.Format ("SELECT BaseRate FROM FeesAndFines WHERE EffectiveDate > {0}", DateTime.Now);
					using (SqlDataReader reader = cmd.ExecuteReader())
					{
						while (reader.Read())
						{
							baseConsume = Convert.ToDecimal (reader ["BaseRate"]);
							
							break;
						}
						reader.Close();
					}
				}
				catch (SqlException ex)
				{
					Console.WriteLine(ex.Message);
					Console.WriteLine(ex.StackTrace);
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}
			
			return baseConsume;
		}

        public void SaveWaterReadingList(List<WaterReadingWS> list)
        {
            foreach (var wr in list)
            {
                SaveWaterReading(wr);
            }
        }

        private void SaveWaterReading(WaterReadingWS wr)
        {
            SqlConnection connection = new SqlConnection(connectionString);

            using (var cmd = connection.CreateCommand())
            {
                try
                {
                    connection.Open();
					cmd.CommandText = @"INSERT INTO WaterReadingControl (WaterReadingId, ReaderId, KardexId, CurrentMeasurerRead, PreviousMeasurerRead, PaidAmount, ReadingDate, WaterFare, ReadingState, WaterCollectControlId) 
														         VALUES (@waterReadingId, @readerId, @kardexId, @currentMeasurerRead, @previousMeasurerRead, @paidAmount, @readingDate, @waterFare, @readingState, @waterCollectControlId);";

                    cmd.Parameters["@waterReadingId"].Value = wr.WaterReadingId;
                    cmd.Parameters["@readerId"].Value = wr.ReaderId;
                    cmd.Parameters["@kardexId"].Value = wr.KardexId;
                    cmd.Parameters["@currentMeasurerRead"].Value = wr.CurrentMeasurerRead;
                    cmd.Parameters["@previousMeasurerRead"].Value = wr.PreviousMeasurerRead;
					cmd.Parameters["@paidAmount"].Value = wr.PaidAmount;					
                    cmd.Parameters["@readingDate"].Value = wr.ReadingState;
					cmd.Parameters["@waterFare"].Value = wr.WaterFare;
                    cmd.Parameters["@readingState"].Value = wr.ReadingState;
					cmd.Parameters["@waterCollectControlId"].Value = wr.WaterReadingId;

                    cmd.ExecuteNonQuery();

                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }

        }
 
		public void SaveKardexList(List<KardexWS> list)
		{
			foreach (var k in list)
			{
				SaveKardex(k);
			}
		}
		
		private void SaveKardex(KardexWS k)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			
			using (var cmd = connection.CreateCommand())
			{
				try
				{
					connection.Open();
					cmd.CommandText = @"UPDATE Kardex
										SET WaterMeasurer = @waterMeasurer,
											HasAWaterMeasurer = @hasAWaterMeasurer
										WHERE KardexId = @kardexId";
					
					cmd.Parameters["@waterMeasurer"].Value = k.WaterMeasurer;
					cmd.Parameters["@hasAWaterMeasurer"].Value = k.HasAWaterMeasurer;
					cmd.Parameters["@kardexId"].Value = k.KardexID;
					
					cmd.ExecuteNonQuery();
				}
				catch (SqlException ex)
				{
					Console.WriteLine(ex.Message);
					Console.WriteLine(ex.StackTrace);
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}
		}

		public void SaveCutSupplyList(List<CutSupplyWS> list)
		{
			foreach (var cs in list)
			{
				SaveCutSupply(cs);
				ChangeReconnectionState(cs);
			}
		}
		
		private void SaveCutSupply(CutSupplyWS cs)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			
			using (var cmd = connection.CreateCommand())
			{
				try
				{
					connection.Open();
					cmd.CommandText = @"UPDATE CutSupply
										SET CurrentReading = @currentReading
										WHERE CutSupplyID = @cutSupplyID";
					
					cmd.Parameters["@currentReading"].Value = cs.CurrentReading;
					cmd.Parameters["@cutSupplyID"].Value = cs.CutSupplyID;

					cmd.ExecuteNonQuery();
				}
				catch (SqlException ex)
				{
					Console.WriteLine(ex.Message);
					Console.WriteLine(ex.StackTrace);
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}
		}

		private void ChangeReconnectionState(CutSupplyWS cs)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			
			using (var cmd = connection.CreateCommand())
			{
				try
				{
					connection.Open();
					cmd.CommandText = @"UPDATE Reconnection
										SET Eneabled = @eneabled
										WHERE ReconnectionID = @reconnectionID";
					
					cmd.Parameters["@eneabled"].Value = true;
					cmd.Parameters["@reconnectionID"].Value = cs.CutSupplyID;
					
					cmd.ExecuteNonQuery();
				}
				catch (SqlException ex)
				{
					Console.WriteLine(ex.Message);
					Console.WriteLine(ex.StackTrace);
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}
		}

		public void SaveReconnectionList(List<ReconnectionWS> list)
		{
			foreach (var r in list)
			{
				SaveReconnection(r);
			}
		}
		
		private void SaveReconnection(ReconnectionWS r)
		{
			SqlConnection connection = new SqlConnection(connectionString);
			
			using (var cmd = connection.CreateCommand())
			{
				try
				{
					connection.Open();
					cmd.CommandText = @"UPDATE Reconnection
										SET CurrentReading = @currentReading
										WHERE ReconnectionID = @reconnectionID";
					
					cmd.Parameters["@currentReading"].Value = r.CurrentReading;
					cmd.Parameters["@reconnectionID"].Value = r.ReconnectionID;
					
					cmd.ExecuteNonQuery();
				}
				catch (SqlException ex)
				{
					Console.WriteLine(ex.Message);
					Console.WriteLine(ex.StackTrace);
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}
		}

		public void SaveCollectControlList(List<WaterReadingWS> list)
		{
			foreach (var wr in list)
			{
				SaveCollectControl(wr);
			}
		}
		
		private void SaveCollectControl(WaterReadingWS wr)
        {
            SqlConnection connection = new SqlConnection(connectionString);

            using (var cmd = connection.CreateCommand())
            {
                try
                {
                    connection.Open();
					cmd.CommandText = @"INSERT INTO CollectControl ( CollectControlId,ReadingDate,PaymentAmount,KardexId,UserId,IsPaid )
												            VALUES ( @collectControlId,@readingDate,@paymentAmount,@kardexId,@userId,@isPaid );";

                    cmd.Parameters.Add(new SqlParameter("@collectControlId", DbType.Int32));
                    cmd.Parameters.Add(new SqlParameter("@readingDate", DbType.DateTime));
					cmd.Parameters.Add(new SqlParameter("@paymentAmount", DbType.Decimal));
                    cmd.Parameters.Add(new SqlParameter("@kardexId", DbType.Int32));
                    cmd.Parameters.Add(new SqlParameter("@userId", DbType.String));
                    cmd.Parameters.Add(new SqlParameter("@isPaid", DbType.Binary));

                    cmd.Parameters[0].Value = wr.WaterReadingId;
                    cmd.Parameters[1].Value = wr.ReadingDate;
                    cmd.Parameters[2].Value = wr.PaidAmount;
                    cmd.Parameters[3].Value = wr.KardexId;
                    cmd.Parameters[4].Value = wr.ReaderId;
                    cmd.Parameters[5].Value = false;

                    cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
		
	}
}